import json
import time
import flask
import random
import requests

import numpy as np

from datetime import datetime, timedelta

from .datas import showDatas
from .database import dbExec, dbVisExec, COL_NAMES_SET, TABLE_NAME, TABLE_NAMES

APP = flask.app.Flask(__name__)

def setData(tableName: str, data: dict[str, object]):
    for name in {tableName} | data.keys():
        assert(all(map(lambda char: 'a' <= char <= 'z', name.lower())))
    print(f'setData: {data = }')
    colNames = list(data.keys())
    dbExec(
        'INSERT INTO {} ({}) VALUES ({})'.format(
            tableName, ', '.join(colNames),
            ', '.join(['%s'] * len(colNames))
        ),
        *(data.get(name) for name in colNames)
    )
    return data

@APP.route('/add/raw', methods = ['POST'])
def rawAdd():
    postData = flask.request.json
    if (tableName := postData.get('table')) not in TABLE_NAMES:
        return {'error': f'table "{tableName}" not valid'}, 404
    if not isinstance((data := postData.get('data')), dict):
        return {'error': f'data {data} not a dict'}, 404
    try:
        return setData(tableName, data)
    except Exception as e:
        return {'exception': str(e)}, 404

@APP.route('/add', methods = ['POST'])
def add():
    postData = flask.request.form
    data = {key: value for key, value in postData.items() if key in COL_NAMES_SET}
    data.update({key: 0 for key in COL_NAMES_SET if key not in data})
    return setData(TABLE_NAME, data)

@APP.route('/del', methods = ['POST'])
def delData():
    return '本接口暂不开放', 404
    postData = flask.request.form
    data = {key: value for key, value in postData.items()}
    print(f'{data = }')
    bandId = getDatabase().erase(data.get('stuId'))
    data['bandId'] = bandId
    return json.dumps(data, ensure_ascii = False, indent = 4)

@APP.route('/show', methods = ['GET'])
def showData():
    names = 'stuId, bandId, stuName, timeIn, timeOut, calorie'.split(', ')
    result = dbExec(f'SELECT {", ".join(names)} FROM {TABLE_NAME} LIMIT 100')
    return showDatas((row.get(name) for name in names) for row in result)

@APP.route('/count/stuNum/now', methods = ['GET'])
def countStuNow():
    return {'count': 2}
    result = dbExec(f'SELECT COUNT(*) from {TABLE_NAME} where timeOut IS NULL')
    return {'count': result[0].get('COUNT(*)')}

@APP.route('/count/stuNum/period/clockIn', methods = ['GET'])
def countStuPeriodClockIn():
    return {'count': 2}
    param = flask.request.args
    print(f'{param = }')
    try:
        timeFrom = datetime.strptime(param.get('from'), '%Y-%m-%d %H:%M:%S')
    except Exception as e:
        return {'exception': str(e)}, 500
    _timeTo = param.get('to')
    if _timeTo:
        try:
            timeTo = datetime.strptime(_timeTo, '%Y-%m-%d %H:%M:%S')
        except Exception as e:
            return {'exception': str(e)}, 500
        result = dbExec(
            f'SELECT COUNT(*) FROM {TABLE_NAME} WHERE timeIn >= %s AND timeIn <= %s',
            timeFrom, timeTo
        )
    else:
        result = dbExec(
            f'SELECT COUNT(*) FROM {TABLE_NAME} WHERE timeIn >= %s',
            timeFrom
        )
    return {'count': result[0].get('COUNT(*)')} 

@APP.route('/count/calorie/today', methods = ['GET'])
def countCalorieToday():
    return {'count': 990.2}
    result = dbExec(
        f'SELECT SUM(calorie) FROM {TABLE_NAME} WHERE '
        + 'calorie IS NOT NULL AND timeIn >= %s',
        datetime.now() - timedelta(days = 1)
    )
    return {'count': result[0].get('SUM(calorie)') or 0.}

@APP.route('/rank/calorie/today', methods = ['GET'])
def rankCalorieToday():
    param = flask.request.args
    try:
        maxCount = int(param.get('maxCount', 100))
    except Exception as e:
        return {'exception': str(e)}, 500
    maxCount = max(1, maxCount)
    return {
        'stuList': [
            {'id': '18801336897', 'name': '严若天', 'calorie': 122.09},
            {'id': '900', 'name': 'name', 'calorie': 23.9}
        ]
    }
    result = dbExec(
        f'SELECT *, RANK() OVER (ORDER BY _sum DESC) AS _rank '
        + f'FROM (SELECT DISTINCT stuId as _stuId, stuName as _stuName, '
        + f'(SELECT SUM(calorie) FROM {TABLE_NAME} WHERE '
        + f'stuId = _stuId) AS _sum FROM {TABLE_NAME} WHERE '
        + f'calorie IS NOT NULL AND timeIn >= %s) AS _abc LIMIT {maxCount}',
        datetime.now() - timedelta(days = 1)
    )
    return {'stuList': [{
        'id': row.get('_stuId'),
        'name': row.get('_stuName'),
        'calorie': row.get('_sum'),
    } for row in result]}

@APP.route('/rank/clockIn/monthly', methods = ['GET'])
def rankClockInMonthly():
    param = flask.request.args
    try:
        maxCount = int(param.get('maxCount', 100))
    except Exception as e:
        return {'exception': str(e)}, 500
    maxCount = max(1, maxCount)
    now = datetime.now()
    result = dbExec(
        'SELECT *, RANK() OVER (ORDER BY _count DESC) AS _rank FROM ({}) AS _bcd LIMIT {}'.format(
            'SELECT DISTINCT stuId AS _stuId, stuName AS _stuName, ({}) AS _count FROM ({}) AS _abc'.format(
                f'SELECT COUNT(*) FROM {TABLE_NAME} WHERE stuId = _stuId',
                f'SELECT * FROM {TABLE_NAME} WHERE timeIn >= %s AND timeOut IS NOT NULL'
            ), maxCount
        ),
        now - timedelta(days = now.day + 1)
    )
    return {'stuList': [{
        'id': row.get('_stuId'),
        'name': row.get('_stuName'),
        'count': row.get('_count'),
    } for row in result]}

@APP.route('/count/stuNum/monthly/reachStd', methods = ['GET'])
def countStuMonthlyReachStd():
    return '本接口暂不开放', 404

def getFromWZH(stuId):
    '''
    从手环的后端处获得数据

    ```json
    [
        {
            "battery": 102,
            "blood_oxygen": 98,
            "heart_rate": 61,
            "length": "01",
            "mac": "D1:FD:30:C0:71:3E",
            "steps": 0,
            "temperature": 26.58,
            "time": "Wed, 05 Jun 2024 08:13:12 GMT",
            "type": "ff"
        },
        ...,
        {
            "battery": 101,
            "blood_oxygen": 98,
            "heart_rate": 79,
            "length": "01",
            "mac": "D1:FD:30:C0:71:3E",
            "steps": 0,
            "temperature": 31.74,
            "time": "Thu, 06 Jun 2024 06:15:23 GMT",
            "type": "ff"
        }
    ]
    ```
    '''
    yrtId: int = dbVisExec(f'SELECT id FROM fa_user WHERE mobile="{stuId}"')[0]['id']
    print(f'{yrtId = }')
    userInfo: dict[str, str | int] = dbVisExec(f'SELECT createtime, deviceno FROM fa_user_sport WHERE user_id={yrtId} ORDER BY createtime DESC LIMIT 1')[0]
    bindTime = userInfo['createtime']
    print(f'{bindTime = }')
    bindTime = datetime.fromtimestamp(bindTime)
    print(f'{bindTime = }')
    totalSec = (datetime.now() - bindTime).total_seconds()
    totalSec = min(totalSec, 60 * 15)  # 为了防止请求过慢，最多要15分钟的数据
    macAddr = userInfo['deviceno']
    print(f'{macAddr = }')
    url = f'https://smartring-test.lo-li.co/data_in_period?mac={macAddr}&period={int(totalSec)}'  # 获取数据
    print(f'{url = }')
    s = time.time()
    try:
        resp: list[dict[str, str | float | int]] = requests.get(url).json()
    except Exception as e:
        print('exception from wzh:', e)
        # raise e
        # 这里用一个临时的resp代替
        resp = [{
            'heart_rate': 80,
            'time': datetime.now().strftime('%a, %d %b %Y %H:%M:%S GMT'),
            'steps': 10,
        }]
    e = time.time()
    print(f'total time waiting data from WZH: {e - s} seconds')
    print(f'{resp = }')
    rates = [item['heart_rate'] for item in resp]
    times = [datetime.strptime(item['time'], '%a, %d %b %Y %H:%M:%S GMT') for item in resp]
    steps = max(item['steps'] for item in resp)
    minSteps = min(item['steps'] for item in resp)
    steps -= minSteps
    return {
        'calorie': (steps * 1.80 * 0.37 * 70 * 0.87 / 1000) or 10,  # 步数 * 身高m * 0.37 * 体重kg * 0.87
        'time': (max(times) - min(times)).total_seconds() or 10,
        'meanRate': np.mean(rates),
        'maxRate': max(rates)
    }

@APP.route('/detail/stuId', methods = ['GET'])
def getStuDetail():
    # print('in /detail/stuId')
    param = flask.request.args
    try:
        stuId = str(param.get('id'))
    except Exception as e:
        return {'exception': str(e)}, 500
    if stuId == '18801336897' or True:
        try:
            res = getFromWZH(stuId)
            print(f'{res = }')
            return res
        except Exception as e:
            print(e)
    time = 20 + random.random() * 100
    calorie = (7.5 + random.random()) * time
    meanRate = 100 + random.random() * 20
    maxRate = meanRate + random.random() * 20
    res = {
        'calorie': 0,
        'time': 0,
        'meanRate': 0,
        'maxRate': 0,
    }
    # print(f'{res = }')
    return res
